/**
 * Holds all of the data for connecting to a relational database which may be
 * Oracle or MySQL. Also has methods for creating the database Connection. Reads
 * HOSTS.cfg and finds assemblies starting with PAVE on each machine
 */
package jpave.dataholders;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import assem.DBConn;
import jpave.dataholders.GlobalSettings;
import util.Debug.ErrorReport;
import util.methods.HostsCfg;
import util.methods.StringHelpers;

public class DatabaseData implements Serializable, Comparable<DatabaseData> {
	private static final boolean DEBUG = false;

	public DatabaseData() {}

	/**
	 * Creates a DatabaseData object
	 * @param path path to the database, including User/Password
	 */
	public DatabaseData(String path) {
		this();
		if (!StringHelpers.isEmpty(path)) {
			String[] tokens = path.split(":");
			if (tokens.length > 0) setDB_URL(tokens[0]);
			if (tokens.length > 1) setDBName(tokens[1]);
			if (tokens.length > 2) setAnnotateUser(tokens[2]);
			if (tokens.length > 3) setAnnotatePassword(tokens[3]);
			if (tokens.length > 2) setQueryUser(tokens[2]);
			if (tokens.length > 3) setQueryPassword(tokens[3]);
		}
	}

	/**
	 * database type (DB_TYPE_MY_SQL | DB_TYPE_ORACLE)
	 */
	public void setDBType(int n) {nDBType = n;}
	public int getDBType() {return nDBType;}
	
	public String toString() {return getFull_DB_URL();}

	public void setDBName(String str) {strDB_Name = str;}
	public String getDBName() {return strDB_Name;}

	public void setQueryUser(String str) {strQueryUser = str;}
	public String getQueryUser() {return strQueryUser;}

	public void setQueryPassword(String str) {strQueryPassword = (str == null ? "" : str);}
	public String getQueryPassword() {return strQueryPassword;}

	public String getAnnotateUser() {return strAnnotateUser;}
	public void setAnnotateUser(String annotateUser) {strAnnotateUser = annotateUser;}

	public String getAnnotatePassword() {return strAnnotatePassword;}
	public void setAnnotatePassword(String annotatePassword) {
		strAnnotatePassword = annotatePassword;}
	
	public void setDB_URL(String str) {strDB_URL = str;}
	public String getDB_URL() {return strDB_URL;}

	public String getFull_DB_URL() {
		switch (nDBType) {
		case DB_TYPE_MY_SQL:
			return "jdbc:mysql://" + strDB_URL + "/" + strDB_Name;
		case DB_TYPE_ORACLE:
			return "jdbc:oracle:thin:@" + strDB_URL + ":" + strDB_Name;
		}

		throw new RuntimeException("Unrecognized database type.");
	}

	public String getDriverClass() {
		switch (nDBType) {
		case DB_TYPE_MY_SQL:
			return "com.mysql.jdbc.Driver";
		case DB_TYPE_ORACLE:
			return "oracle.jdbc.driver.OracleDriver";
		}

		throw new RuntimeException("Unrecognized database type.");
	}

	public Connection createDBConnection() throws Exception {
		return (new HostsCfg()).getDBConn(strDB_Name).getDBconn();
	}

	public int compareTo(DatabaseData db2) {
		DatabaseData db1 = this;
		int nRes = db1.getFull_DB_URL().compareTo(db2.getFull_DB_URL());
		if (nRes != 0)
			return nRes;
		nRes = db1.strDB_Name.compareTo(db2.strDB_Name);
		if (nRes != 0)
			return nRes;
		return 0;
	}

	/**
	 * reads the assemblies in the current database
	 * @return vector of assembly IDs
	 * called from AssemblyData
	 */
	public Vector<String> getAssemblyIDs() {
		Vector<String> ids = new Vector<String>();
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = createDBConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT assemblyid FROM assembly");
			while (rs.next()) {
				ids.add(rs.getString("assemblyid"));
			}
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		} catch (Exception err) { 
			String s = "Warning: no assembly for " + strDB_Name + " on host " + strDB_URL;
			ErrorReport.reportError(err, s);
		}
		return ids;
	}

	static public Vector<DatabaseData> loadAllPAVEonHost(String hostURL,
			String qusername, String qpassword, String ausername,
			String apassword, int nDBType) {
		
		Vector<DatabaseData> list = new Vector<DatabaseData>();
		try 
		{
			HostsCfg hosts = new HostsCfg();
			DBConn dbc = hosts.getDBConn("mysql");
			ResultSet rs = dbc.executeQuery("show databases");
			String dbName;
			while (rs.next()) 
			{
				dbName = rs.getString(1);
				if (DEBUG)
					System.err.println("loadDBListFromHost: " + hostURL + " "
							+ dbName);
				if ((dbName.startsWith(GlobalSettings.STCW) || dbName.startsWith("PAVE")) && !dbName.startsWith("PAVE_web")
						&& !dbName.endsWith("TEMP")) {
					DatabaseData newDB = new DatabaseData();
					newDB.setDB_URL(hostURL);
					newDB.setDBName(dbName);
					newDB.setQueryUser(qusername);
					newDB.setQueryPassword(qpassword);
					newDB.setAnnotateUser(ausername);
					newDB.setAnnotatePassword(apassword);
					newDB.nDBType = nDBType;
					list.add(newDB);
				}
			}
			if (list.size() == 0)
			{
				System.err.println("No TCW databases were found on host: " + hosts.host);
				System.err.println("Does your database user (" + hosts.user + ") have sufficient permissions to 'show databases'?");
				System.exit(0);
			}
		} 
		catch (Exception err) 
		{
			String s = "Error accessing database on " + hostURL;
			ErrorReport.reportError(err, s);
		}

		return list;
	}

	public final static int DB_TYPE_ORACLE = 1;
	public final static int DB_TYPE_MY_SQL = 2;
	
	// Database connection information:
	private String strDB_URL = "";
	private String strDB_Name = "";
	private String strQueryUser = "";
	private String strQueryPassword = "";
	private String strAnnotateUser = "";
	private String strAnnotatePassword = "";
	private int nDBType = DB_TYPE_MY_SQL;

	private static final long serialVersionUID = 1;
}
